var express = require('express');
var router = express.Router();
const Quert = require('../utils/db.js')

router.get('/', async function (req, res, next) {
    res.send('列表')
});

//地区
router.get('/areas', async function (req, res, next) {
    let sql = `SELECT * FROM e_area`
    let { err, result } = await Quert(sql)

    //出现错误时显示
    if (err) {

        next({
            mag: '出现错误咯',
            status: 500
        })
        return
    }
    let data = {
        msg: "ok",
        status: 200,
        result
    }
    res.send(data)
});
// 年级
router.get('/grades', async function (req, res, next) {
    let sql = `SELECT * FROM e_grade`
    let { err, result } = await Quert(sql)

    //出现错误时显示
    if (err) {

        next({
            mag: '出现错误咯',
            status: 500
        })
        return
    }
    let data = {
        msg: "ok",
        status: 200,
        result
    }
    res.send(data)
});
// 学科
router.get('/subjects', async function (req, res, next) {
    let sql = `SELECT * FROM e_subject`
    let { err, result } = await Quert(sql)

    //出现错误时显示
    if (err) {

        next({
            mag: '出现错误咯',
            status: 500
        })
        return
    }
    let data = {
        msg: "ok",
        status: 200,
        result
    }
    res.send(data)
});
// 考试类别
router.get('/cates', async function (req, res, next) {
    let sql = `SELECT * FROM e_category`
    let { err, result } = await Quert(sql)

    //出现错误时显示
    if (err) {

        next({
            mag: '出现错误咯',
            status: 500
        })
        return
    }
    let data = {
        msg: "ok",
        status: 200,
        result
    }
    res.send(data)
});
// 课程类表
router.get('/courses', async function (req, res, next) {
    // let page = 1;
    // let size = 5;

    let { page = 1, size = 5, area = '', grade = '', subject = '', category = '' } = req.query

    page = page + ''
    if (page.indexOf('.') != -1) {
        next({
            msg: '请传递正确的页码',
            status: 500
        })
        return
    }
    page = Number(page)

    size = size + ''
    if (size.indexOf('.') != -1) {
        next({
            msg: '请传递正确的页码',
            status: 500
        })
        return
    }
    size = Number(size)
    let startIndex = (page - 1) * size;

    let whereStr = ''
    if (area != '') {
        if (whereStr == '') {
            whereStr += ` WHERE area_name='${area}' `
        } else {
            whereStr += ` AND area_name='${area}' `
        }
    }

    if (grade != '') {
        if (whereStr == '') {
            whereStr += ` WHERE grade_name='${grade}' `
        } else {
            whereStr += ` AND grade_name='${grade}' `
        }
    }

    if (subject != '') {
        if (whereStr == '') {
            whereStr += ` WHERE subject_name='${subject}' `
        } else {
            whereStr += ` AND subject_name='${subject}' `
        }
    }

    if (category != '') {
        if (whereStr == '') {
            whereStr += ` WHERE category_name='${category}' `
        } else {
            whereStr += ` AND category_name='${category}' `
        }
    }


    let sql1 = `SELECT id,cid,title,price,CONCAT('http://localhost:3000/uploads/courses/',image_src) AS image_src,type_name,area_name,subject_name,type,category_name,tid,status,grade_name FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${startIndex},${size}`

    let sql2 = `SELECT * FROM e_course  ${whereStr} `

    let res1 = await Quert(sql1)
    let res2 = await Quert(sql2)
    let rows = res2.result.length
    let totalPage = Math.ceil(rows / size)

    let data = {
        msg: "ok",
        status: 200,
        result: {
            page,
            totalPage,
            rows,
            data: res1.result
        }

    }
    res.send(data)
});


router.get('/search',async (req,res,next)=>{
    let { page = 1, size = 5 ,keyword} = req.query

    page = page + ''
    if (page.indexOf('.') != -1) {
        next({
            msg: '请传递正确的页码',
            status: 500
        })
        return
    }
    page = Number(page)

    size = size + ''
    if (size.indexOf('.') != -1) {
        next({
            msg: '请传递正确的页码',
            status: 500
        })
        return
    }
    size = Number(size)
    let startIndex = (page - 1) * size;

    let sql1 = `SELECT id,cid,type_name,intro,detail_imgs,grade_name,category_name,tid,status, CONCAT('http://localhost:3000/uploads/courses/',image_src) AS image_src,subject_name,title,area_name,price,type FROM e_course WHERE title LIKE '%${keyword}%'  ORDER BY id LIMIT ${startIndex},${size}`

    let sql2 = `SELECT * FROM e_course WHERE title LIKE '%${keyword}%'`

    
    let res1 = await Quert(sql1)
    let res2 = await Quert(sql2)
    let rows = res2.result.length
    let totalPage = Math.ceil(rows / size)

    let data = {
        msg: "ok",
        status: 200,
        result: {
            page,
            totalPage,
            rows,
            keyword,
            data: res1.result
        }
    }
    res.send(data)
})
module.exports = router;
